﻿#region
/**
 * $Date: 0001-1-1
*/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using BuXiaChu.OrderDishes.DAL.DataAccess;
using BuXiaChu.OrderDishes.Model;
#endregion

namespace BuXiaChu.OrderDishes.DAL
{
    public class SysUserInfoDAL
    {
        #region private const
        private const string PARAM_SYSTEM_USER_ID = "@SystemUserId";
        private const string PARAM_EMPLOYEE_ID = "@EmployeeId";
        private const string PARAM_APPLICATION_ID = "@ApplicationId";
        private const string PARAM_LOGIN_NAME = "@LoginName";
        private const string PARAM_PASSWORD = "@Password";
        private const string PARAM_LAST_ACTIVE_DATE = "@LastActiveDate";

        private static readonly string ConnectionString = SQLHelper.CONNECTION_STRING;
        #endregion

        private static void FillModelFrom(SqlDataReader reader, SysUserInfoModel obj)
        {
            #region
            if (reader != null && !reader.IsClosed)
            {
                obj.SystemUserId = reader.IsDBNull(reader.GetOrdinal("SystemUserId")) ? 0 : reader.GetInt32(reader.GetOrdinal("SystemUserId"));
                obj.EmployeeId = reader.IsDBNull(reader.GetOrdinal("EmployeeId")) ? 0 : reader.GetInt32(reader.GetOrdinal("EmployeeId"));
                obj.ApplicationId = reader.IsDBNull(reader.GetOrdinal("ApplicationId")) ? Guid.NewGuid() : reader.GetGuid(reader.GetOrdinal("ApplicationId"));
                obj.LoginName = reader.IsDBNull(reader.GetOrdinal("LoginName")) ? String.Empty : reader.GetString(reader.GetOrdinal("LoginName"));
                obj.Password = reader.IsDBNull(reader.GetOrdinal("Password")) ? String.Empty : reader.GetString(reader.GetOrdinal("Password"));
                obj.LastActiveDate = reader.IsDBNull(reader.GetOrdinal("LastActiveDate")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("LastActiveDate"));
            }
            #endregion
        }

        private static SqlParameter[] GetSysUserInfoParams(SysUserInfoModel obj)
        {
            #region
            SqlParameter[] dbParams ={                    
                     SQLHelper.MakeParam(PARAM_SYSTEM_USER_ID, SqlDbType.Int,0,obj.SystemUserId),
                     SQLHelper.MakeParam(PARAM_EMPLOYEE_ID, SqlDbType.Int,0,obj.EmployeeId),
                     SQLHelper.MakeParam(PARAM_APPLICATION_ID, SqlDbType.UniqueIdentifier,0,obj.ApplicationId),
                     SQLHelper.MakeParam(PARAM_LOGIN_NAME, SqlDbType.NVarChar, 50,obj.LoginName),                    
                     SQLHelper.MakeParam(PARAM_PASSWORD, SqlDbType.NVarChar, 20,obj.Password)               
            };

            return dbParams;
            #endregion
        }

        public static bool Save(SysUserInfoModel obj)
        {
            #region
            try
            {
                SqlParameter[] dbParams = GetSysUserInfoParams(obj);

                if (obj.SystemUserId == 0)
                {
                    obj.SystemUserId = Convert.ToInt32(SQLHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "SysUserInfo_InsertUpdate", dbParams));
                    return obj.SystemUserId > 0 ? true : false;
                }
                else
                {
                    var affectNum = SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "SysUserInfo_InsertUpdate", dbParams);
                    return affectNum > 0 ? true : false;
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("SysUserInfo-->InsertOrUpdate-->" + e.Message);

                ExceptionMessageDAL.Record(ex);
            }

            return false;
            #endregion
        }

        public static SysUserInfoModel GetSysUserInfoById(int systemUserId)
        {
            #region
            SqlDataReader reader = null;
            SysUserInfoModel obj = new SysUserInfoModel();

            try
            {
                SqlParameter[] dbParams = {
                        SQLHelper.MakeParam(PARAM_SYSTEM_USER_ID, SqlDbType.Int, 0, systemUserId)
                    };

                reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "SysUserInfo_GetBySysUserInfoId", dbParams);

                if (reader.Read())
                {
                    FillModelFrom(reader, obj);
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("SysUserInfo-->GetSingleObject-->" + e.Message);

                ExceptionMessageDAL.Record(ex);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }

            return obj;
            #endregion
        }

        public static SysUserInfoModel GetSysUserInfoBy(string loginName, string password, Guid websiteId)
        {
            #region
            SqlDataReader reader = null;
            SysUserInfoModel obj = new SysUserInfoModel();

            try
            {
                SqlParameter[] dbParams = {
                        SQLHelper.MakeParam(PARAM_LOGIN_NAME, SqlDbType.NVarChar, 50, loginName.Trim()),
                        SQLHelper.MakeParam(PARAM_PASSWORD, SqlDbType.NVarChar, 20, password.Trim()),
                        SQLHelper.MakeParam(PARAM_APPLICATION_ID, SqlDbType.UniqueIdentifier, 0,websiteId),
                    };

                reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "SysUserInfo_GetByLoginNameAndPassword", dbParams);

                if (reader.Read())
                {
                    FillModelFrom(reader, obj);

                    if (obj.SystemUserId > 0)
                    {
                        UpdateLastActiveDate(obj.SystemUserId);
                    }
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("SysUserInfo-->GetSysUserInfoBy-->" + e.Message);

                ExceptionMessageDAL.Record(ex);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }

            return obj;
            #endregion
        }

        public static bool UpdateLastActiveDate(int sysUserId)
        {
            #region
            try
            {
                SqlParameter[] dbParams = {
                        SQLHelper.MakeParam(PARAM_SYSTEM_USER_ID, SqlDbType.Int, 0, sysUserId)
                    };

                return SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "SysUserInfo_UpdateLastActiveDate", dbParams) > 0 ? true : false;
            }
            catch (Exception e)
            {
                Exception ex = new Exception("SysUserInfo-->UpdateLastActive-->" + e.Message);

                ExceptionMessageDAL.Record(ex);
            }
            return false;
            #endregion
        }

        public static bool Delete(int systemUserId)
        {
            #region
            try
            {
                SqlParameter[] dbParams = {
                        SQLHelper.MakeParam(PARAM_SYSTEM_USER_ID, SqlDbType.Int, 0, systemUserId)
                    };

                return SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "SysUserInfo_Delete", dbParams) > 0 ? true : false;
            }
            catch (Exception e)
            {
                Exception ex = new Exception("SysUserInfo-->Delete-->" + e.Message);

                ExceptionMessageDAL.Record(ex);
            }
            return false;
            #endregion
        }

        public static List<SysUserInfoModel> Search(string sqlWhere, int pageIndex, int pageSize, out int pageCount)
        {
            #region
            List<SysUserInfoModel> result = new List<SysUserInfoModel>();
            pageCount = 0;
            try
            {
                if (string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere = " 1=1 ";
                }

                SqlParameter[] dbParams =
                {
                    SQLHelper.MakeParam("@sqlWhere", SqlDbType.NVarChar,0,sqlWhere),
                    SQLHelper.MakeParam("@pageIndex",SqlDbType.Int,0,pageIndex),
                    SQLHelper.MakeParam("@pageSize",SqlDbType.Int,0,pageSize),
                    SQLHelper.MakeOutParam("@pageCount", SqlDbType.Int,0,0)                    
                };
                using (SqlDataReader reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "SysUserInfo_Search", dbParams))
                {
                    while (reader.Read())
                    {
                        SysUserInfoModel model = new SysUserInfoModel();
                        FillModelFrom(reader, model);
                        result.Add(model);
                    }
                }
                pageCount = int.Parse(dbParams[3].Value.ToString());
            }
            catch (Exception e)
            {
                Exception ex = new Exception("SysUserInfo-->Search-->" + e.ToString());
                ExceptionMessageDAL.Record(ex);
            }
            return result;
            #endregion
        }
    }
}